
import java.sql.*;
import groovy.sql.Sql
import java.lang.reflect.Array;
import org.apache.commons.mail.EmailAttachment;
import org.apache.commons.mail.MultiPartEmail;
println "开始<br/>"
def mysqlservice ="127.0.0.1:3306/test"
def mysqlusername="root"
def mysqlpsw="aaaaaaaa"
//连接数据库
def sql = Sql.newInstance("jdbc:mysql://${mysqlservice}", mysqlusername,mysqlpsw, "com.mysql.jdbc.Driver")

checkTable sql


//获取所有的发送Email的帐号
def senderList=sql.rows("select * from EmailAccout")
//println senderList

if(senderList.size()<1){
	println "没有发送Email的账户<br/>"
	return
}



def sendMessages=sql.rows("select * from sendEmail limit 0 ,100")
//while开始  内容表存在数据
if(sendMessages.size()>0){
	sendMessages.each {
		
		sendEmail(senderList[2],it)
		
		//将记录保存到指定表
		sql.execute("INSERT INTO `test`.`sendemailok` " +
				"(`sendto`, `username`, `subject`, `content`)" +
				" VALUES (?, ?, ?, ?)",[it.sendto,it.username,it.subject,it.content])
		//删除当前记录
		sql.execute("delete from sendEmail where idsendEmail = ?",[it.idsendEmail])
		
	}
	sendMessages=sql.rows("select * from sendEmail limit 0 ,100")
}else{
println "没有数据需要处理<br/>"
}
//读取需要发送的内容（100条）
//发送邮件的Email帐户，需要轮流使用，等其他规则
//发送消息
//将成功的纪录转移到另外一张表
//end while

//关闭数据库
sql.close()

println "结束<br/>"



def static sendEmail(def username ,def message){
	// 创建一个email
	MultiPartEmail multipartemail = new MultiPartEmail();
	if(username['type']=="qq"){
		multipartemail.setHostName("smtp.qq.com"); // 设置邮件服务器地址
	}else if(username['type']=="163"){
		multipartemail.setHostName("smtp.163.com"); // 设置邮件服务器地址
	}else if(username['type']=="gmail"){
		multipartemail.setSSL(Boolean.TRUE); // 设定是否使用SSL
		multipartemail.setSslSmtpPort("465"); // 设定SSL端口
		multipartemail.setHostName("smtp.gmail.com"); // 设置邮件服务器地址
		
	}else{
		multipartemail.setHostName("smtp.qq.com"); // 设置邮件服务器地址
		
	}
	multipartemail.addTo(message['sendto'], message['username']);// 对方
	multipartemail.setFrom(username['user'], username['name']);// 我方
	multipartemail.setAuthentication(username['user'], username['psw']);
	multipartemail.setSubject(message['subject']);// 标题
	multipartemail.setContent(message['content'], "text/html;charset=GBK")// html
	// 发送邮件
	multipartemail.send();
}



/**
 * 检测必要的表
 * @param sql
 * @return
 */
def static checkTable(def sql){
	//启动前检测配置文件,检测油箱用户表
	//select   1   from   information_schema.tables   where   table_name   =   'EmailAccout ';
	
	if( 0==sql.rows("select   1   from   information_schema.tables   where   table_name   =   'EmailAccout'").size()){
		sql.execute """
				CREATE  TABLE `EmailAccout` (
					  `idEmailAccout` INT NOT NULL AUTO_INCREMENT ,
					  `user` VARCHAR(45) NULL ,
					  `psw` VARCHAR(45) NULL ,
					  `name` VARCHAR(45) NULL ,
					  `type` VARCHAR(45) NULL COMMENT '163,qq,gmail' ,
					  PRIMARY KEY (`idEmailAccout`) )
					COMMENT = 'Email accout';
			"""
	}
	//检测内容表是否存在
	if( 0==sql.rows("select   1   from   information_schema.tables   where   table_name   =   'sendEmail'").size()){
		sql.execute """
							CREATE  TABLE  `sendEmail` (
							  `idsendEmail` INT NOT NULL AUTO_INCREMENT ,
							  `sendto` VARCHAR(45) NULL ,
							  `username` VARCHAR(45) NULL ,
							  `subject` VARCHAR(500) NULL ,
							  `content` VARCHAR(5000) NULL ,
							  `attachment` VARCHAR(100) NULL COMMENT 'like this\n \'\nd:\\\\1.jpg&d:\\\\2jpg\n\'' ,
							  PRIMARY KEY (`idsendEmail`) )
							COMMENT = 'need send email';
				"""
	}
	//检测 发送成功记录存储表
	if( 0==sql.rows("select   1   from   information_schema.tables   where   table_name   =   'sendEmailOk'").size()){
		sql.execute """
							 CREATE  TABLE  `sendEmailOk` (
							  `idsendEmail` INT NOT NULL AUTO_INCREMENT ,
							  `sendto` VARCHAR(45) NULL ,
							  `username` VARCHAR(45) NULL ,
							  `subject` VARCHAR(500) NULL ,
							  `content` VARCHAR(5000) NULL ,
							  `attachment` VARCHAR(100) NULL COMMENT 'like this\n \'\nd:\\\\1.jpg&d:\\\\2jpg\n\'' ,
							  PRIMARY KEY (`idsendEmail`) )
							COMMENT = 'need send email';
				"""
	}
}